package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.ProductBean;
import com.project.dao.IProductDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductDaoImpl extends BaseDao implements IProductDao {
    @Override
    public CutPageBean<ProductBean> findByItem(int pageNO, String name, LocalDate startDate, LocalDate endDate) {
//        CutPageBean<ProductBean> cutBean = new CutPageBean<>();
//        this.setConnection();
//        String sql ="select * from t_product where 1=1 ";
//        String dynaSql ="";
//        if (name != null && name.length()!=0){
//            dynaSql += "and p_name like '%" +name+"%' ";
//        }
//        if (startDate !=null){
//            dynaSql += "and p_createDate >='"+startDate+"' ";
//        }
//        if (endDate !=null){
//            dynaSql += "and p_createDate <='"+endDate+"' ";
//        }
//        try {
//            ps=con.prepareStatement(sql+dynaSql+"limit ?,?");
//            ps.setObject(1,(pageNO-1)*CutPageBean.PAGESIZE);
//            ps.setObject(2,CutPageBean.PAGESIZE);
//            rs = ps.executeQuery();
//            while (rs.next()){
//                cutBean.getList().add(this.getProduct());
//            }
//            ps=con.prepareStatement("select count(*) c from t_product where 1=1 "+dynaSql);
//            rs=ps.executeQuery();
//            if (rs.next()){
//                cutBean.setCount(rs.getInt("c"));
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }finally {
//            this.close();
//        }
        /**防止SQL注入*/
        CutPageBean<ProductBean> cutBean = new CutPageBean<>();
        this.setConnection();
        List valueList = new ArrayList();
        String dynaSql ="where 1=1 ";
        if (name!=null && name.length()!=0){
            dynaSql+="and p_name like ? ";
            valueList.add("%"+name+"%");
        }
        if (startDate!=null){
            dynaSql+="and p_createDate >=? ";
            valueList.add(startDate);
        }
        if (endDate!=null){
            dynaSql+="and p_createDate <=? ";
            valueList.add(endDate);
        }

        try {
            ps=con.prepareStatement("select * from t_product "+dynaSql+"limit ?,?");
            for (int i = 0; i <valueList.size() ; i++) {
                ps.setObject(i+1,valueList.get(i));
            }
            ps.setObject(valueList.size()+1,(pageNO-1)*CutPageBean.PAGESIZE);
            ps.setObject(valueList.size()+2,CutPageBean.PAGESIZE);

            rs=ps.executeQuery();
            while (rs.next()){
                cutBean.getList().add(this.getProduct());
            }

            ps=con.prepareStatement("select count(*) c from t_product " + dynaSql);
            for (int i = 0; i <valueList.size() ; i++) {
                ps.setObject(i+1,valueList.get(i));
            }
            rs=ps.executeQuery();
            if (rs.next()){
                cutBean.setCount(rs.getInt("c"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return cutBean;
    }

    @Override
    public ProductBean findById(Integer id) {
        ProductBean product=null;
        this.setConnection();
        try {
            ps=con.prepareStatement("select  * from t_product where pk_productId =?");
            ps.setObject(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                product=getProduct();

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.close();
        }

        return product;
    }

    @Override
    public void update(Integer id, Integer price) {
        this.setConnection();
        try {
            ps=con.prepareStatement("update t_product set p_price=? where pk_productId=?");
            ps.setObject(1,price);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.close();
        }

    }

    private ProductBean getProduct() throws SQLException {
        ProductBean product = new ProductBean();
        product.setId(rs.getInt("pk_productId"));
        product.setName(rs.getString("p_name"));
        product.setCreateDate(LocalDate.parse(rs.getString("p_createDate")));
        product.setPrice(rs.getInt("p_price"));
        return product;
    }
}
